package cn.dida.dao;

import cn.dida.model.LgsCust;
import cn.dida.model.Pageable;
import cn.dida.util.DateUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhangzhiling on 2017/4/21.
 */
@Repository
public class LgsCustDao extends DaoBase {


    public Pageable<LgsCust> query(Integer companyId, String keyword, Integer state,  int page){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from lgs_bd_cust bd left join lgs_bd_area a on a.id=bd.area_id ");
        StringBuffer sqlQueryResult = new StringBuffer("select bd.id, bd.cust_code 'custCode', bd.cust_name 'custName', bd.company_id 'companyId',bd.source, " +
                "bd.area_id 'areaId',bd.addr, bd.cust_prop 'custProp',bd.state,bd.create_time 'createTime',bd.creator,bd.creator_name 'creatorName',a.areaname 'areaName' " +
                "from lgs_bd_cust bd left join lgs_bd_area a on a.id=bd.area_id ");

        StringBuffer sqlWhere = new StringBuffer(" where bd.cust_prop=1 and bd.company_id = ? ") ;
        args = ArrayUtils.add(args, companyId);
        if(keyword != null && keyword != ""){
            sqlWhere.append(" and bd.cust_name like ? ");
            args = ArrayUtils.add(args, "%" + keyword + "%");
        }
        if(state != null){
            sqlWhere.append(" and bd.state = ? ");
            args = ArrayUtils.add(args, state);
        }


        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append(" order by bd.create_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<LgsCust> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsCust.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    public Integer insert(LgsCust lgsCust){

        String sql = "insert into lgs_bd_cust(cust_code, cust_name, company_id, area_id, addr, cust_prop," +
                "state,create_time,creator_name, source) " +
                "values (:custCode, :custName, :companyId, :areaId, :addr, :custProp," +
                ":state, :createTime, :creatorName, :source)";

        lgsCust.setCreateTime(new Date().getTime());
        KeyHolder keyHolder=new GeneratedKeyHolder();
        SqlParameterSource ps=new BeanPropertySqlParameterSource(lgsCust);
        namedParameterJdbcTemplate.update(sql, ps, keyHolder);
        return keyHolder.getKey().intValue();
    }


    public Integer update(LgsCust lgsCust){

        String sql = "update lgs_bd_cust set cust_code=:custCode, cust_name=:custName, area_id=:areaId,state=:state," +
                "addr=:addr where id =" + lgsCust.getId();

        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("custCode", lgsCust.getCustCode());
        paramMap.put("custName", lgsCust.getCustName());
        paramMap.put("areaId", lgsCust.getAreaId());
        paramMap.put("state", lgsCust.getState());
        paramMap.put("addr", lgsCust.getAddr());

        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    public LgsCust getLgsCust(int id) {
        String sql = "select bd.id, bd.cust_code 'custCode', bd.cust_name 'custName', bd.company_id 'companyId',bd.source, " +
                "bd.area_id 'areaId',bd.addr, bd.cust_prop 'custProp',bd.state,bd.create_time 'createTime',bd.creator,bd.creator_name 'creatorName'," +
                "bd.mobile, bd.owner, a.areaname 'areaName' from lgs_bd_cust bd left join lgs_bd_area a on a.id=bd.area_id where bd.id = ?";
        List<LgsCust> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsCust>(LgsCust.class), id);
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 获取同一公司下状态正常的 客户对象（收发货对象用）
     * @param companyId
     * @param state
     * @return
     */
    public List<LgsCust> getList(Integer companyId, Integer state){

        String sql = "select bd.id, bd.cust_code 'custCode', bd.cust_name 'custName', bd.company_id 'companyId', bd.source," +
                "bd.area_id 'areaId',bd.addr, bd.cust_prop 'custProp',bd.state,bd.create_time 'createTime',bd.creator,bd.creator_name 'creatorName'" +
                " from lgs_bd_cust bd  where bd.cust_prop=1 and bd.company_id = ? and bd.state = ?";

        List<LgsCust> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LgsCust.class), companyId, state);
        return result;
    }

    /**
     * 根据输入对象名称或编码  获取列表
     * @param keyword
     * @param limit
     * @param companyId
     * @return
     */
    public List<LgsCust> getSearchList(String keyword, Integer limit,Integer companyId) {

        String sql = "select bd.id, bd.cust_code 'custCode', bd.cust_name 'custName', bd.company_id 'companyId', bd.source," +
                "bd.area_id 'areaId',bd.addr, bd.cust_prop 'custProp',bd.state,bd.create_time 'createTime',bd.creator,bd.creator_name 'creatorName'" +
                " from lgs_bd_cust bd  where bd.cust_prop=1 and bd.company_id = ? and (bd.cust_name like ? or bd.cust_code like ?) limit ?";

        List<LgsCust> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LgsCust.class), companyId, keyword+"%","%"+keyword+"%",limit);
        return result;
    }

    /**
     * 验证客户编码是否已存在
     * @param companyId
     * @param custCode
     * @return
     */
    public LgsCust testCode( Integer companyId, String custCode){

        String sql = "select bd.id, bd.cust_code 'custCode', bd.cust_name 'custName', bd.company_id 'companyId', " +
                "bd.area_id 'areaId',bd.addr, bd.cust_prop 'custProp',bd.state,bd.create_time 'createTime',bd.creator,bd.creator_name 'creatorName'" +
                " from lgs_bd_cust bd  where bd.cust_prop=1 and bd.company_id = ? and bd.cust_code = ?" ;
        List<LgsCust>  list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsCust>(LgsCust.class), companyId, custCode);
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 获取区域列表
     * @param companyId
     * @return
     */
    public List<Map<String,Object>> getAreaList(Integer companyId){
        String sql = "select id, areacode, areaname from lgs_bd_area bd  where company_id = ? and state = 1" ;
        List<Map<String,Object>>  list = jdbcTemplate.queryForList(sql, companyId);
        return list;
    }

    /**************************** 进货网点 ****************************/
    /**
     * 进货网点列表
     * @param companyId
     * @param keyword
     * @param custCode
     * @param mobile
     * @param page
     * @return
     */
    public Pageable<LgsCust> queryCustList(Integer companyId, String keyword, String custCode, String mobile, int page){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from lgs_bd_cust bd left join lgs_bd_area a on a.id=bd.area_id ");
        StringBuffer sqlQueryResult = new StringBuffer("select bd.id, bd.cust_code 'custCode', bd.cust_name 'custName', bd.company_id 'companyId',bd.source, " +
                "bd.area_id 'areaId',bd.addr, bd.cust_prop 'custProp',bd.state,bd.create_time 'createTime',bd.creator,bd.creator_name 'creatorName'," +
                "bd.mobile, bd.owner, a.areaname 'areaName' " +
                "from lgs_bd_cust bd left join lgs_bd_area a on a.id=bd.area_id ");

        StringBuffer sqlWhere = new StringBuffer(" where bd.cust_prop=2 and source=3  and bd.company_id = ? ") ;
        args = ArrayUtils.add(args, companyId);
        if(keyword != null && keyword != ""){
            sqlWhere.append(" and bd.cust_name like ? ");
            args = ArrayUtils.add(args, "%" + keyword + "%");
        }
        if(custCode != null){
            sqlWhere.append(" and bd.cust_code = ? ");
            args = ArrayUtils.add(args, custCode);
        }
        if(mobile != null){
            sqlWhere.append(" and bd.mobile = ? ");
            args = ArrayUtils.add(args, mobile);
        }


        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append(" order by bd.create_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<LgsCust> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsCust.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }


    public Pageable<Map<String,Object>> queryCustScanList(Integer companyId, Integer branchId, String product, String boxCode,
                                                          String scanTimeS, String scanTimeE, int page){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from lgs_log_cust_scan sc left join product p on p.id=sc.product_id " +
                                                        " left join pbc pb on pb.id=p.pbc_id ");
        StringBuffer sqlQueryResult = new StringBuffer("select sc.id, sc.company_id,sc.scan_time scanTime, sc.box_id boxId, " +
                "p.name, p.number, pb.pcount " +
                "from lgs_log_cust_scan sc left join product p on p.id=sc.product_id  left join pbc pb on pb.id=p.pbc_id ");

        StringBuffer sqlWhere = new StringBuffer(" where sc.branch_id=? and sc.company_id = ? ") ;
        args = ArrayUtils.add(args, branchId);
        args = ArrayUtils.add(args, companyId);
        if(product != null && product != ""){
            sqlWhere.append(" and (p.name like ? or p.number = ?)");
            args = ArrayUtils.add(args, "%" + product + "%");
            args = ArrayUtils.add(args,  product );
        }
        if(boxCode != null){
            sqlWhere.append(" and sc.box_id = ? ");
            args = ArrayUtils.add(args, boxCode);
        }
        if(StringUtils.isNotBlank(scanTimeS)){//操作时间（开始）
            sqlWhere.append(" and sc.scan_time >= ?");
            try {
                scanTimeS = String.valueOf(DateUtils.stringToLong(scanTimeS, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(scanTimeS);
            args = ArrayUtils.add(args, ctimes);
        }
        if(StringUtils.isNotBlank(scanTimeE)){//操作时间（结束）
            sqlWhere.append(" and sc.scan_time <= ?");
            try {
                scanTimeE = String.valueOf(DateUtils.stringToLong(scanTimeE, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimee = Long.parseLong(scanTimeE);
            args = ArrayUtils.add(args, ctimee);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append(" order by sc.scan_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }
}
